﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class sqlFields : System.Web.UI.Page
{

    public string txtCont = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        bindData();

    }

    private void bindData()
    {
        iData.DataAcess worker = new iData.DataAcess();
        string sql = @"select ROW_NUMBER() OVER(order by id) AS i,ISNULL(b.value,0) as tbltitle,a.name as tblname from (
select o.name,o.id from sysobjects AS o
where o.xtype='U') as a left join (
select m.value,m.major_id from sys.extended_properties AS m
where  m.minor_id=0 ) as b on a.id=b.major_id";
        var dset = worker.Execute(sql);

        rptTblList.DataSource = dset;
        rptTblList.DataBind();

        rptTblAllList.DataSource = dset;
        rptTblAllList.DataBind();

        txtResult.Text = txtCont;
    }

    private System.Data.DataSet getTableFields(string tblName, string tbltitle)
    {
        iData.DataAcess worker = new iData.DataAcess();
        string sql = @"select ROW_NUMBER() OVER(order by colid) AS i,isnull(m.value,x.colname+'**') as memo,x.colname,x.typename,x.fdlength,x.[isnull],x.colid,x.tblname 
from 
(Select o.name as tblname,c.name as colname,t.name as typename,c.length as fdlength,c.isnullable as [isnull] ,c.colid,o.id as oid 
	from syscolumns as c,systypes as t,sysobjects as o 
	Where o.id=c.id and o.xtype='u' and o.status>=0 and t.xtype=c.xtype 
	and o.name='[tblname]'  and t.name!='sysname'  
) as x 
left join sys.extended_properties as m 
on m.major_id=x.oid and x.colid=m.minor_id 
order by x.tblname,x.colid
";
        sql = sql.Replace("[tblname]", tblName);
        var dset = worker.Execute(sql);
        var sb = new System.Text.StringBuilder();
        sb.Append("----------------------------\r\n");
        sb.AppendFormat("{0},{1},test,2\r\n", tbltitle, tblName);
        string sFldnames = "", sFldTitles = "", sFldTypes = "";
        for (int i = 0; i < dset.Tables[0].Rows.Count; i++)
        {
            var row = dset.Tables[0].Rows[i];
            if (i > 0)
            {
                sFldnames += ","; sFldTitles += ","; sFldTypes += ",";
            }
            string memo = row["memo"].ToString();
            if (memo == "") memo = row["colname"].ToString();
            memo = memo.Split(new char[] { ',', ':', '|', '，', '：' })[0];
            sFldnames += row["colname"].ToString();
            sFldTitles += memo;
            string fdlength = row["fdlength"].ToString();
            string typename = row["typename"].ToString();
            if (typename == "varchar" || typename == "nvarchar")
            {
                typename += "("+fdlength+")";
            }
            sFldTypes += typename;

        }
        sb.Append(sFldTitles + "\r\n" + sFldnames + "\r\n" + sFldTypes + "\r\n\r\n");
        txtCont += sb.ToString();
        return dset;


    }

    protected void rptTblAllList_ItemCommand(object source, RepeaterCommandEventArgs e)
    {

    }
    protected void rptTblAllList_ItemDataBound(object source, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            Repeater rep = e.Item.FindControl("rptFieldList") as Repeater;//找到里层的repeater对象
            System.Data.DataRowView rowv = (System.Data.DataRowView)e.Item.DataItem;//找到分类Repeater关联的数据项 
            string tbltitle = rowv["tbltitle"].ToString();
            string tblName = rowv["tblName"].ToString();
            rep.DataSource = getTableFields(tblName, tbltitle);
            rep.DataBind();
        }
    }

}